今天講 SQL 語法~~
SQL 全稱「結構化資料庫查詢語言」,由六大關鍵語法組合而成,select, insert, update, delete, where, group by,簡單來說就是在資料庫撈取資料的特定語言。各大資料庫廠商的 SQL 在制定上接受 ANSI 規範,雖不是完全一樣但大同小異,學習起來很有價值。
假設有一張資料表長這樣:
INSERT INTO member (mem_name, mem_nick, mem_permissions, ...)
VALUES (值1, 值2, 值3, ...);
SELECT *
FROM member;
讀取特定欄位
SELECT mem_name
FROM member;
讀取特定會員,使用篩選器 where 條件句
SELECT *
FROM member where mem_id = 1
更新資料通常會搭配 where 語句
UPDATE member
SET mem_nick = "David"
WHERE mem_id = 1;
DELETE FROM member
WHERE mem_id = 2;
以上是最基礎的 sql 語法,更多更進階可以參考這個網站。
SQL 寫的好可以為後端語言省去很多處理上的功夫。
以下以 PHP 為例,如何查詢 member ID 為 1 的會員的資料,並回傳前端:
<?php
try{
require_once("connectBooks.php");
$sql = "select * from member where mem_id = ?;";
$member_PDO = $pdo->prepare($sql);
$member_PDO->bindValue(1,1); //第一個?綁1這個值進去
$member_PDO->execute();
if( $member_PDO->rowCount() == 0 ){ //找不到的話
echo "查無資料";
}else{ //找得到的話
$member = $member_PDO->fetchObject();//取回該row全部資料,然後放進 $facility這個變數中
$mem_id = $member->mem_id;
$mem_nick = $member->mem_nick;//以php物件的操作方式來取出每個欄位的資料,並放進變數中
$password = $member->password;
$mem_name = $member->mem_name;
$mem_points = $member->mem_points;
$mem_permissions = $member->mem_permissions;
$mem_mail = $member->mem_mail;
$mem_phone = $member->mem_phone;
$member_arry = array( //把從資料庫中撈的資料放進陣列中
"mem_id" => $mem_id,
"mem_nick" => $mem_nick,
"password" => $password,
"mem_name" => $mem_name,
"mem_points" => $mem_points,
"mem_permissions" => $mem_permissions,
"mem_mail" => $mem_mail,
"mem_phone" => $mem_phone
);
echo json_encode( $member_arry ); //最後再將php的array轉成json並echo出去
}
}catch(PDOException $e){
echo $e->getMessage();
}
?>